<?php

class DatabaseController extends Backend {

    public function init() {
        parent::init();
        $this->_view->assign(array('title' => '数据库', 'nickname' => 'Database', 'intro' => 'SQL语句查询、数据库表结构修复和优化等'));
    }

    function indexAction() {
        $tables = array();
        $tables_data_length = $tables_index_length = $tables_free_length = $tables_data_count = 0;
        //$this->db->query("SET GLOBAL INNODB_STATS_ON_METADATA = 0");
        //$tablelist = $this->db->query("SHOW TABLE STATUS FROM `{$this->db->info['name']}`")->fetchAll();
        //$this->db->query("SET GLOBAL INNODB_STATS_ON_METADATA = 1");
        $tablelist = array();
        foreach ($tablelist as $m => $row) {
            $tables[] = $row;
            $tables_data_length += $row['Data_length'];
            $tables_data_count += $row['Rows'];
            $tables_index_length += $row['Index_length'];
            $tables_free_length += $row['Data_free'];
        }
        $tables = $this->get_tables();
        $data['tables'] = $tables;
        $data['tables_data_length'] = $tables_data_length;
        $data['tables_data_count'] = $tables_data_count;
        $data['tables_index_length'] = $tables_index_length;
        $data['tables_free_length'] = $tables_free_length;
        /*
          $one = $this->db->one("SELECT * FROM {pre}config WHERE name='sql'");
          $saved_sql = array();
          if ($one && $one['content'])
          $saved_sql = explode('###', $one['content']);

          $data['saved_sql'] = array_values(array_filter($saved_sql));
         * */
        $data['saved_sql'] = array();
        $this->_view->assign($data);
    }

    function get_tables() {
        $tables = $list = array();
        $this->db->query("SHOW TABLES");
        while ($row = $this->db->fetch()) {
            $tables[] = array('Name' => reset($row), 'Rows' => 0);
        }
        return $tables;
    }

    public function queryAction() {
        $do_action = $this->_request->getPost('do_action');

        echo '<style type="text/css">
            xmp,body{margin:0;padding:0;line-height:18px;font-size:14px;font-family:Microsoft Yahei,Verdana;background-color:#f5f5f5;}
            hr{height:1px;margin:5px 1px;background:#e3e3e3;border:none;}
            </style>';
        if ($do_action == '')
            Tools::error("未知的操作类型", "javascript:;");
        $tablename = $this->_request->getPost("tablename", array());
        if (in_array($do_action, array('doquery', 'optimizeall', 'repairall'))) {
            $sqlquery = $this->_request->getPost("sqlquery");
            $this->$do_action();
        } else if (count($tablename) == 0) {
            Tools::error("未选择操作的数据库", "javascript:;");
        } else {
            foreach ($tablename as $m => $n) {
                $name = $n;
                $this->$do_action($n);
            }
        }
        return FALSE;
    }

    function viewinfo($name) {
        $row = $this->db->all("SHOW CREATE TABLE {$name}");
        $row = array_values($row[0]);
        $info = $row[1];
        echo "<xmp>{$info};</xmp><br />";
    }

    function viewdata($name = '') {
        $sqlquery = "SELECT * FROM {$name}";
        $this->doquery($sqlquery);
    }

    function optimize($name = '') {
        if ($this->db->all("OPTIMIZE TABLE {$name}")) {
            echo "优化表[{$name}]成功<br />";
        } else {
            echo "优化表[{$name}]失败<br />";
        }
    }

    function optimizeall($name = '') {
        $this->db->query("SHOW TABLES", array(), "optimize");
        while ($row = $this->db->fetch("optimize")) {
            $name = reset($row);
            if ($this->db->all("OPTIMIZE TABLE {$name}")) {
                echo "优化表[{$name}]成功<br />";
            } else {
                echo "优化表[{$name}]失败<br />";
            }
        }
    }

    function repair($name = '') {
        if ($this->db->all("REPAIR TABLE {$name}")) {
            echo "修复表[{$name}]成功<br />";
        } else {
            echo "修复表[{$name}]失败<br />";
        }
    }

    function repairall($name = '') {
        $this->db->query("SHOW TABLES", array(), "repair");
        while ($row = $this->db->fetch('repair')) {
            $name = reset($row);
            if ($this->db->all("REPAIR TABLE {$name}")) {
                echo "修复表[{$name}]成功<br />";
            } else {
                echo "修复表[{$name}]失败<br />";
            }
        }
    }

    function backup() {
        $tables = $this->get_tables();
        $filename = date("YmdHis") . ".sql";
        $backup_dir = DATAPATH . "database/";
        if (!is_dir($backup_dir)) {
            mkdir($backup_dir);
        }
        $fp = @fopen($backup_dir . $filename, 'w');
        foreach ($tables as $table => $item) {
            dump_table($item['Name'], $fp);
        }
        fclose($fp);
        echo Tools::translate('GENERAL_DATABASE_BACKUP_SUCCESS', $filename);
    }

    function doquery($sql = null) {
        $sqlquery = $sql ? $sql : $_POST['sqlquery'];
        if ($sqlquery == '')
            exit("SQL语句不能为空");
        $sqlquery = str_replace("\r", "", $sqlquery);
        $sqls = preg_split("/;[ \t]{0,}\n/i", $sqlquery);

        $r = '';
        foreach ($sqls as $key => $val) {
            if (trim($val) == '')
                continue;

            $r .= "运行SQL：<span style='color:green;'>{$val}</span>";

            //运行查询语句
            if (preg_match("/^(select|explain)(.*)/i ", $val)) {
                Logger::getInstance()->start("query");
                $this->db->query($val);
                $count = $this->db->count();

                $limit = stripos(strtolower($val), "limit") !== false ? true : false;

                $arr = Logger::getInstance()->stop("query");
                $usedseconds = "耗时：" . $arr['microtimeused'] . "!<br />";
                if ($count <= 0) {
                    $r .= "返回结果为空";
                } else {
                    $r .= ("共有" . $count . "条记录! " . (!$limit && $count > 100 ? "最大返回100条" : ""));
                }
                $r .= $usedseconds;
                $j = 0;
                while ($n = $this->db->fetch()) {
                    $j++;
                    if (!$limit && $j > 100)
                        break;
                    $r .= "<hr/>";
                    $r .= "<span style='color:red'>记录：$j</span><br />";
                    foreach ($n as $k => $v) {
                        $r .= "<font color='blue'>{$k}：</font>{$v}<br/>\r\n";
                    }
                }
            } else {
                $this->db->query($val);
                $arr = Logger::getInstance()->stop("query");
                $r .="共影响" . $this->db->count() . "条记录! 耗时：{$arr['microtimeused']}!<br />";
            }
        }
        echo $r;
    }

}

//数据库辅助函数
//获取表的名称
function list_tables($database) {
    $rs = mysql_list_tables($database);
    $tables = array();
    while ($row = mysql_fetch_row($rs)) {
        $tables[] = $row[0];
    }
    mysql_free_result($rs);
    return $tables;
}

//导出数据库
function dump_table($table, $fp = null) {
    $need_close = false;
    if (is_null($fp)) {
        $fp = fopen($table . '.sql', 'w');
        $need_close = true;
    }
    $a = mysql_query("show create table `{$table}`");
    $row = mysql_fetch_assoc($a);
    fwrite($fp, $row['Create Table'] . ';'); //导出表结构
    $rs = mysql_query("SELECT * FROM `{$table}`");
    while ($row = mysql_fetch_row($rs)) {
        fwrite($fp, get_insert_sql($table, $row));
    }
    mysql_free_result($rs);
    if ($need_close) {
        fclose($fp);
    }
}

//导出表数据
function get_insert_sql($table, $row) {
    $sql = "INSERT INTO `{$table}` VALUES (";
    $values = array();
    foreach ($row as $value) {
        $values[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $sql .= implode(', ', $values) . ");";
    return $sql;
}
